Skip Headers

Oracle® Call Interface Programmer's Guide, 10g Release 2 (10.2) Part Number B14250-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 6 Describing Schema Metadata

This chapter discusses the use of the OCIDescribeAny() function to obtain information about schema elements.

This chapter contains these topics:

- Using OCIDescribeAny()

- Parameter Attributes

- Character Length Semantics Support in Describing

- Examples Using OCIDescribeAny()

## Using OCIDescribeAny()

The `OCIDescribeAny()` function enables you to perform an explicit describe of the following schema objects and their subschema objects:

- tables and views

- synonyms

- procedures

- functions

- packages

- sequences

- collections

- types

- schemas

- databases

Information about other schema elements (function arguments, columns, type attributes, and type methods) is available through a describe of one of the preceding schema objects or an explicit describe of the subschema object.

When an application describes a table, it can then retrieve information about that table's columns. Additionally, `OCIDescribeAny()` can directly describe subschema objects such as columns of a table, packages of a function, or fields of a type if given the name of the subschema object.

The `OCIDescribeAny()` call requires a describe handle as one of its arguments. The describe handle must be previously allocated with a call to OCIHandleAlloc().

The information returned by `OCIDescribeAny()` is organized hierarchically like a tree, as shown in Figure 6-1:

Figure 6-1 OCIDescribeAny() Table Description

Description of "Figure 6-1 OCIDescribeAny() Table Description"

The describe handle returned by `OCIDescribeAny()` has an attribute, `OCI_ATTR_PARAM`, that points to such a description tree. Each node of the tree has attributes associated with that node, and attributes that are like recursive describe handles and point to subtrees containing further information. If all the attributes are homogenous, as in the case of elements of a column list, they are called parameters. The attributes associated with any node are returned by `OCIAttrGet()`, and the parameters are returned by `OCIParamGet()`.

A call to `OCIAttrGet()` on the describe handle for the table returns a handle to the column-list information. An application can then use `OCIParamGet()` to retrieve the handle to the column description of a particular column in the column-list. The handle to the column descriptor can be passed to `OCIAttrGet()` to get further information about the column, such as the name and datatype.

After a SQL statement is executed, information about the select-list is available as an attribute of the statement handle. No explicit describe call is needed. To retrieve information about select-list items from the statement handle, the application must call `OCIParamGet()` once for each position in the select-list to allocate a parameter descriptor for that position.

Note: No subsequent `OCIAttrGet()` or `OCIParamGet()` call requires extra round trips, as all the description is cached on the client side by `OCIDescribeAny()`.

### Limitations on OCIDescribeAny()

The `OCIDescribeAny()` call limits information returned to the basic information and stops expanding a node if it amounts to another describe. For example, if a table column is of an object type, then the OCI does not return a subtree describing the type since this information can be obtained by another describe.

The table name is not returned by `OCIDescribeAny()` or the implicit use of `OCIStmtExecute()`. Sometimes a column is not associated with a table. In most cases, the table is already known.

See Also: "Describing Select-list Items" "OCIDescribeAny()"

### Notes on Types and Attributes

When performing describe operations, you should be aware of the following:

#### Datatype Codes

`OCI_ATTR_TYPECODE` returns typecodes which represent the types supplied by the user when a new type is created using the `CREATE TYPE` statement. These typecodes are of the enumerated type `OCITypeCode`, and are represented by `OCI_TYPECODE` constants. Internal PL/SQL types (boolean, indexed table) are not supported.

`OCI_ATTR_DATA_TYPE` returns typecodes which represent the datatypes stored in database columns. These are similar to the describe values returned by previous versions of Oracle. These values are represented by SQLT constants (`ub2` values). BOOLEAN types return SQLT_BOL.

See Also: "Typecodes" for more information about typecodes, such as the `OCI_TYPCODE` values returned in the `OCI_ATTR_TYPECODE` attribute and the SQLT typecodes returned in the `OCI_ATTR_DATA_TYPE` attribute

#### Describing Types

In order to describe type objects, it is necessary to initialize the OCI process in object mode:

    /* Initialize the OCI Process */
     if (OCIEnvCreate((OCIEnv **) &envhp, (ub4) OCI_OBJECT, (dvoid *) 0,
                      (dvoid * (*)(dvoid *,size_t)) 0,
                      (dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
                      (void (*)(dvoid *, dvoid *)) 0, (size_t) 0, (dvoid **) 0))
     {
       printf("FAILED: OCIEnvCreate()\n");
       return OCI_ERROR;
     }

See Also: "OCIInitialize()"

#### Note on Implicit and Explicit Describes

The column attribute `OCI_ATTR_PRECISION` can be returned using an implicit describe with `OCIStmtExecute()` and an explicit describe with `OCIDescribeAny()`. When using an implicit describe, the precision should be set to `sb2`. When using an explicit describe, the precision should be set to `ub1` for a placeholder. This is necessary to match the datatype of precision in the dictionary.

#### Note on OCI_ATTR_LIST_ARGUMENTS

The `OCI_ATTR_LIST_ARGUMENTS` attribute for type methods represents second-level arguments for the method.

For example, given the following record `my_type` and the procedure `my_proc` which takes an argument of type `my_type:`

    my_type record(a number, b char)
    my_proc (my_input my_type)

the `OCI_ATTR_LIST_ARGUMENTS` attribute would apply to arguments `a` and `b` of the `my_type` record.

## Parameter Attributes

A parameter is returned by `OCIParamGet()`. Parameters can describe different types of objects or information, and have attributes depending on the type of description they contain, or type-specific attributes. This section describes the attributes and handles that belong to different parameters.

Note that `OCIDescribeAny()` does support more than two name components, for example, `schema.type.attr1.attr2.method1`. Note that with more than one component, the first component is interpreted as the schema name (unless some other flag is set). There is a flag to specify that the object must be looked up under PUBLIC, that is, describe "a", where "a" can be either in the current schema or a public synonym.

If you do not know what the object type is, specify `OCI_PTYPE_UNK`. Otherwise an error is returned if the actual object type does not match the specified type.

Table 6-1 lists the attributes of all parameters:

Table 6-1 Attributes of All Parameters

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_NUM_PARAMS` | The number of parameters |
    ub2
`OCI_ATTR_OBJ_ID` | Object or schema ID |
    ub4
`OCI_ATTR_OBJ_NAME` | Database name or object name in a schema |
    OraText *
`OCI_ATTR_OBJ_SCHEMA` | Schema name where the object is located |
    OraText *
`OCI_ATTR_PTYPE` | Type of information described by the parameter. Possible values: `OCI_PTYPE_TABLE` - table `OCI_PTYPE_VIEW` - view `OCI_PTYPE_PROC` - procedure `OCI_PTYPE_FUNC` - function `OCI_PTYPE_PKG` - package `OCI_PTYPE_TYPE` - type `OCI_PTYPE_TYPE_ATTR` - attribute of a type `OCI_PTYPE_TYPE_COLL` - collection type information `OCI_PTYPE_TYPE_METHOD` - method of a type `OCI_PTYPE_SYN` - synonym `OCI_PTYPE_SEQ` - sequence `OCI_PTYPE_COL` - column of a table or view `OCI_PTYPE_ARG` - argument of a function or procedure `OCI_PTYPE_TYPE_ARG` - argument of a type method `OCI_PTYPE_TYPE_RESULT` - results of a method `OCI_PTYPE_LIST` - column list for tables and views, argument list for functions and procedures, or subprogram list for packages. `OCI_PTYPE_SCHEMA` - schema `OCI_PTYPE_DATABASE` - database `OCI_PTYPE_UNK` - unknown schema object |
    ub1
`OCI_ATTR_TIMESTAMP` | The timestamp of the object on which the description is based in Oracle date format |
    ub1 *

The following sections list the attributes and handles specific to different types of parameters.

### Table or View Parameters

Parameters for a table or view (type `OCI_PTYPE_TABLE` or `OCI_PTYPE_VIEW`) have the following type-specific attributes:

Table 6-2 Attributes of Tables or Views

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_OBJID` | Object id |
    ub4
`OCI_ATTR_NUM_COLS` | Number of columns |
    ub2
`OCI_ATTR_LIST_COLUMNS` | Column list (type `OCI_PTYPE_LIST`) |
    dvoid *
`OCI_ATTR_REF_TDO` | REF to the TDO of the base type in case of extent tables |
    OCIRef*
`OCI_ATTR_IS_TEMPORARY` | Indicates the table is temporary |
    ub1
`OCI_ATTR_IS_TYPED` | Indicates the table is typed |
    ub1
`OCI_ATTR_DURATION` | Duration of a temporary table. Values can be: `OCI_DURATION_SESSION` - session `OCI_DURATION_TRANS` - transaction `OCI_DURATION_NULL` -table not temporary |
    OCIDuration

The following are additional attributes which belong to tables:

Table 6-3 Attributes Specific to Tables

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_RDBA` | Data block address of the segment header |
    ub4
`OCI_ATTR_TABLESPACE` | Tablespace the table resides in |
    word
`OCI_ATTR_CLUSTERED` | Indicates the table is clustered |
    ub1
`OCI_ATTR_PARTITIONED` | Indicates the table is partitioned |
    ub1
`OCI_ATTR_INDEX_ONLY` | Indicates the table is index-only |
    ub1

### Procedure, Function, Subprogram Attributes

When a parameter is for a procedure or function (type `OCI_PTYPE_PROC` or `OCI_PTYPE_FUNC`), it has the following type specific attributes:

Table 6-4 Attribute of Procedures or Functions

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_LIST_ARGUMENTS` | Argument list. See "List Attributes". |
    dvoid *
`OCI_ATTR_IS_INVOKER_RIGHTS` | Indicates the procedure or function has invoker's rights |
    ub1

The following attributes are defined only for package subprograms:

Table 6-5 Attributes Specific to Package Subprograms

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_NAME` | Name of the procedure or function |
    OraText *
`OCI_ATTR_OVERLOAD_ID` | Overloading ID number (relevant in case the procedure or function is part of a package and is overloaded). Values returned may be different from direct query of a PL/SQL function or procedure. |
    ub2

### Package Attributes

When a parameter is for a package (type `OCI_PTYPE_PKG`), it has the following type specific attributes:

Table 6-6 Attributes of Packages

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_LIST_SUBPROGRAMS` | Subprogram list. See "List Attributes". |
    dvoid *
`OCI_ATTR_IS_INVOKER_RIGHTS` | Is the package invoker's rights? |
    ub1

### Type Attributes

When a parameter is for a type (type `OCI_PTYPE_TYPE`), it has the attributes listed in Table 6-7. These attributes are only valid if the application initialized the OCI process in `OCI_OBJECT` mode in a call to `OCIInitialize()`.

Table 6-7 Attributes of Types

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_REF_TDO` | Returns the in-memory REF of the type descriptor object for the type, if the column type is an object type. If space has not been reserved for the OCIRef, then it is allocated implicitly in the cache. The caller can then pin the TDO with `OCIObjectPin()`. |
    OCIRef *
`OCI_ATTR_TYPECODE` | Typecode. See "Datatype Codes". Currently can be only `OCI_TYPECODE_OBJECT` or `OCI_TYPECODE_NAMEDCOLLECTION`. |
    OCITypeCode
`OCI_ATTR_COLLECTION_TYPECODE` | Typecode of collection if type is collection; invalid otherwise. See "Datatype Codes". Currently can be only `OCI_TYPECODE_VARRAY` or `OCI_TYPECODE_TABLE`. Error is returned if this attribute is queried for non-collection type. |
    OCITypeCode
`OCI_ATTR_IS_INCOMPLETE_TYPE` | Indicates this is an incomplete type |
    ub1
`OCI_ATTR_IS_SYSTEM_TYPE` | Indicates this is a system type |
    ub1
`OCI_ATTR_IS_PREDEFINED_TYPE` | Indicates this is a predefined type |
    ub1
`OCI_ATTR_IS_TRANSIENT_TYPE` | Indicates this is a transient type |
    ub1
`OCI_ATTR_IS_SYSTEM_GENERATED_TYPE` | Indicates this is a system-generated type |
    ub1
`OCI_ATTR_HAS_NESTED_TABLE` | This type contains a nested table attribute |
    ub1
`OCI_ATTR_HAS_LOB` | This type contains a LOB attribute |
    ub1
`OCI_ATTR_HAS_FILE` | This type contains a `BFILE` attribute |
    ub1
`OCI_ATTR_COLLECTION_ELEMENT` | Handle to collection element. See "Collection Attributes". |
    dvoid *
`OCI_ATTR_NUM_TYPE_ATTRS` | Number of type attributes |
    ub2
`OCI_ATTR_LIST_TYPE_ATTRS` | List of type attributes. See "List Attributes". |
    dvoid *
`OCI_ATTR_NUM_TYPE_METHODS` | Number of type methods |
    ub2
`OCI_ATTR_LIST_TYPE_METHODS` | List of type methods. See "List Attributes". |
    dvoid *
`OCI_ATTR_MAP_METHOD` | Map method of type. See "Type Method Attributes". |
    dvoid *
`OCI_ATTR_ORDER_METHOD` | Order method of type. See "Type Method Attributes". |
    dvoid *
`OCI_ATTR_IS_INVOKER_RIGHTS` | Indicates the type has invoker's rights |
    ub1
`OCI_ATTR_NAME` | A pointer to a string which is the type attribute name |
    OraText *
`OCI_ATTR_SCHEMA_NAME` | A string with the schema name where the type has been created |
    OraText *
`OCI_ATTR_IS_FINAL_TYPE` | Indicates this is a final type |
    ub1
`OCI_ATTR_IS_INSTANTIABLE_TYPE` | Indicates this is an instantiable type |
    ub1
`OCI_ATTR_IS_SUBTYPE` | Indicates this is a subtype |
    ub1
`OCI_ATTR_SUPERTYPE_SCHEMA_NAME` | Name of the schema that contains the supertype |
    OraText *
`OCI_ATTR_SUPERTYPE_NAME` | Name of the supertype |
    OraText *

### Type Attribute Attributes

When a parameter is for an attribute of a type (type `OCI_PTYPE_TYPE_ATTR`), it has the attributes listed in Table 6-8.

Table 6-8 Attributes of Type Attributes

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_DATA_SIZE` | The maximum size of the type attribute. This length is returned in bytes and not characters for strings and raws. It returns 22 for `NUMBER`s. |
    ub4
`OCI_ATTR_TYPECODE` | Typecode. See "Datatype Codes". |
    OCITypeCode
`OCI_ATTR_DATA_TYPE` | The datatype of the type attribute. See "Datatype Codes". |
    ub2
`OCI_ATTR_NAME` | A pointer to a string which is the type attribute name |
    OraText *
`OCI_ATTR_PRECISION` | The precision of numeric type attributes. If the precision is nonzero and scale is -127, then it is a `FLOAT`, else it is a `NUMBER(precision, scale)`. For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. |
    ub1 for explicit describe
    sb2 for implicit describe
`OCI_ATTR_SCALE` | The scale of numeric type attributes. If the precision is nonzero and scale is -127, then it is a `FLOAT`, else it is a `NUMBER(precision, scale)`. For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. |
    sb1
`OCI_ATTR_TYPE_NAME` | A string which is the type name. The returned value will contain the type name if the datatype is `SQLT_NTY` or `SQLT_REF`. If the datatype is `SQLT_NTY`, the name of the named datatype's type is returned. If the datatype is `SQLT_REF`, the type name of the named datatype pointed to by the `REF` is returned |
    OraText *
`OCI_ATTR_SCHEMA_NAME` | A string with the schema name under which the type has been created |
    OraText *
`OCI_ATTR_REF_TDO` | Returns the in-memory `REF` of the TDO for the type, if the column type is an object type. If space has not been reserved for the `OCIRef`, then it is allocated implicitly in the cache. The caller can then pin the TDO with `OCIObjectPin()`. |
    OCIRef *
`OCI_ATTR_CHARSET_ID` | The character set id, if the type attribute is of a string/character type |
    ub2
`OCI_ATTR_CHARSET_FORM` | The character set form, if the type attribute is of a string/character type |
    ub1
`OCI_ATTR_FSPRECISION` | The fractional seconds precision of a datetime or interval. |
    ub1
`OCI_ATTR_LFPRECISION` | The leading field precision of an interval. |
    ub1

### Type Method Attributes

When a parameter is for a method of a type (type `OCI_PTYPE_TYPE_METHOD`), it has the attributes listed in Table 6-9.

Table 6-9 Attributes of Type Methods

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_NAME` | Name of method (procedure or function) |
    OraText *
`OCI_ATTR_ENCAPSULATION` | Encapsulation level of the method (either `OCI_TYPEENCAP_PRIVATE` or `OCI_TYPEENCAP_PUBLIC`) |
    OCITypeEncap
`OCI_ATTR_LIST_ARGUMENTS` | Argument list. See "Note on OCI_ATTR_LIST_ARGUMENTS", and "List Attributes". |
    dvoid *
`OCI_ATTR_IS_CONSTRUCTOR` | Indicates method is a constructor |
    ub1
`OCI_ATTR_IS_DESTRUCTOR` | Indicates method is a destructor |
    ub1
`OCI_ATTR_IS_OPERATOR` | Indicates method is an operator |
    ub1
`OCI_ATTR_IS_SELFISH` | Indicates method is selfish |
    ub1
`OCI_ATTR_IS_MAP` | Indicates method is a map method |
    ub1
`OCI_ATTR_IS_ORDER` | Indicates method is an order method |
    ub1
`OCI_ATTR_IS_RNDS` | Indicates "Read No Data State" is set for method |
    ub1
`OCI_ATTR_IS_RNPS` | Indicates "Read No Process State" is set for method |
    ub1
`OCI_ATTR_IS_WNDS` | Indicates "Write No Data State" is set for method |
    ub1
`OCI_ATTR_IS_WNPS` | Indicates "Write No Process State" is set for method |
    ub1
`OCI_ATTR_IS_FINAL_METHOD` | Indicates this is a final method |
    ub1
`OCI_ATTR_IS_INSTANTIABLE_METHOD` | Indicates this is an instantiable method |
    ub1
`OCI_ATTR_IS_OVERRIDING_METHOD` | Indicates this is an overriding method |
    ub1

### Collection Attributes

When a parameter is for a collection type (type `OCI_PTYPE_COLL`), it has the attributes listed in Table 6-10.

Table 6-10 Attributes of Collection Types

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_DATA_SIZE` | The maximum size of the type attribute. This length is returned in bytes and not characters for strings and raws. It returns 22 for `NUMBER`s. |
    ub4
`OCI_ATTR_TYPECODE` | Typecode. See "Datatype Codes". |
    OCITypeCode
`OCI_ATTR_DATA_TYPE` | The datatype of the type attribute. See "Datatype Codes". |
    ub2
`OCI_ATTR_NUM_ELEMS` | The number of elements in an array. It is only valid for collections that are arrays |
    ub4
`OCI_ATTR_NAME` | A pointer to a string which is the type attribute name |
    OraText *
`OCI_ATTR_PRECISION` | The precision of numeric type attributes. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a `NUMBER(precision, scale)`. For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. |
    ub1 for explicit describe
    sb2 for implicit describe
`OCI_ATTR_SCALE` | The scale of numeric type attributes. If the precision is nonzero and scale is -127, then it is a `FLOAT`, else it is a `NUMBER(precision, scale)`. For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. |
    sb1
`OCI_ATTR_TYPE_NAME` | A string which is the type name. The returned value will contain the type name if the datatype is `SQLT_NTY` or `SQLT_REF`. If the datatype is `SQLT_NTY`, the name of the named datatype's type is returned. If the datatype is `SQLT_REF`, the type name of the named datatype pointed to by the REF is returned |
    OraText *
`OCI_ATTR_SCHEMA_NAME` | A string with the schema name under which the type has been created |
    OraText *
`OCI_ATTR_REF_TDO` | Returns the in-memory REF of the TDO for the type, if the column type is an object type. If space has not been reserved for the OCIRef, then it is allocated implicitly in the cache. The caller can then pin the TDO with `OCIObjectPin()`. |
    OCIRef *
`OCI_ATTR_CHARSET_ID` | The character set id, if the type attribute is of a string/character type |
    ub2
`OCI_ATTR_CHARSET_FORM` | The character set form, if the type attribute is of a string/character type |
    ub1

### Synonym Attributes

When a parameter is for a synonym (type `OCI_PTYPE_SYN`), it has the attributes listed in Table 6-11.

Table 6-11 Attributes of Synonyms

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_OBJID` | Object id |
    ub4
`OCI_ATTR_SCHEMA_NAME` | A string containing the schema name of the synonym translation |
    OraText *
`OCI_ATTR_NAME` | A `NULL`-terminated string containing the object name of the synonym translation |
    OraText *
`OCI_ATTR_LINK` | A `NULL`-terminated string containing the database link name of the synonym translation |
    OraText *

### Sequence Attributes

When a parameter is for a sequence (type `OCI_PTYPE_SEQ`), it has the attributes listed in Table 6-12.

Table 6-12 Attributes of Sequences

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_OBJID` | Object id | ub4
`OCI_ATTR_MIN` | Minimum value (in Oracle `NUMBER` format) | ub1 *
`OCI_ATTR_MAX` | Maximum value (in Oracle `NUMBER` format) | ub1 *
`OCI_ATTR_INCR` | Increment (in Oracle `NUMBER` format) |
    ub1 *
`OCI_ATTR_CACHE` | Number of sequence numbers cached; zero if the sequence is not a cached sequence (in Oracle `NUMBER` format) |
    ub1 *
`OCI_ATTR_ORDER` | Whether the sequence is ordered |
    ub1
`OCI_ATTR_HW_MARK` | High-water mark (in `NUMBER` format) |
    ub1 *

See Also: "OCINumber Examples"

### Column Attributes

Note: For BINARY_FLOAT and BINARY_DOUBLE: If `OCIDescribeAny()` is used to retrieve the column datatype (`OCI_ATTR_DATA_TYPE`) for `BINARY_FLOAT` or `BINARY_DOUBLE` columns in a table, it returns the internal datatype code. The SQLT codes corresponding to the internal datatype codes for `BINARY_FLOAT` and `BINARY_DOUBLE` are `SQLT_IBFLOAT` and `SQLT_IBDOUBLE`.

When a parameter is for a column of a table or view (type `OCI_PTYPE_COL`), it has the attributes listed in Table 6-13.

Table 6-13 Attributes of Columns of Tables or Views

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_CHAR_USED` | Returns the type of length semantics of the column. 0 means byte-length semantics and 1 means character-length semantics. See "Character Length Semantics Support in Describing". |
    ub4
`OCI_ATTR_CHAR_SIZE` | Returns the column character length which is the number of characters allowed in the column. It is the counterpart of `OCI_ATTR_DATA_SIZE` which gets the byte length. See "Character Length Semantics Support in Describing". |
    ub2
`OCI_ATTR_DATA_SIZE` | The maximum size of the column. This length is returned in bytes and not characters for strings and raws. It returns 22 for `NUMBER`s. |
    ub4
`OCI_ATTR_DATA_TYPE` | The datatype of the column. See "Datatype Codes". |
    ub2
`OCI_ATTR_NAME` | A pointer to a string which is the column name |
    OraText *
`OCI_ATTR_PRECISION` | The precision of numeric columns. If the precision is nonzero and scale is -127, then it is a `FLOAT`, else it is a `NUMBER(precision, scale)`. For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. |
    ub1 for explicit describe
    sb2 for implicit describe
`OCI_ATTR_SCALE` | The scale of numeric columns. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a `NUMBER`(precision, scale). For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. |
    sb1
`OCI_ATTR_IS_NULL` | Returns 0 if null values are not permitted for the column |
    ub1
`OCI_ATTR_TYPE_NAME` | Returns a string which is the type name. The returned value will contain the type name if the datatype is `SQLT_NTY` or `SQLT_REF`. If the datatype is `SQLT_NTY`, the name of the named datatype's type is returned. If the datatype is `SQLT_REF`, the type name of the named datatype pointed to by the `REF` is returned |
    OraText *
`OCI_ATTR_SCHEMA_NAME` | Returns a string with the schema name under which the type has been created |
    OraText *
`OCI_ATTR_REF_TDO` | The `REF` of the TDO for the type, if the column type is an object type |
    OCIRef *
`OCI_ATTR_CHARSET_ID` | The character set id, if the column is of a string/character type |
    ub2
`OCI_ATTR_CHARSET_FORM` | The character set form, if the column is of a string/character type |
    ub1

### Argument and Result Attributes

When a parameter is for an argument of a procedure or function (type `OCI_PTYPE_ARG`), for a type method argument (type `OCI_PTYPE_TYPE_ARG`) or for method results (type `OCI_PTYPE_TYPE_RESULT`), it has the attributes listed in Table 6-14.

Table 6-14 Attributes of Arguments and Results

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_NAME` | Returns a pointer to a string which is the argument name |
    OraText *
`OCI_ATTR_POSITION` | The position of the argument in the argument list. Always returns zero. |
    ub2
`OCI_ATTR_TYPECODE` | Typecode. See "Datatype Codes". |
    OCITypeCode
`OCI_ATTR_DATA_TYPE` | The datatype of the argument. See "Datatype Codes". |
    ub2
`OCI_ATTR_DATA_SIZE` | The size of the datatype of the argument. This length is returned in bytes and not characters for strings and raws. It returns 22 for `NUMBER`s. |
    ub4
`OCI_ATTR_PRECISION` | The precision of numeric arguments. If the precision is nonzero and scale is -127, then it is a `FLOAT`, else it is a `NUMBER(precision, scale)`. For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. | `b1` for explicit describe `sb2` for implicit describe
`OCI_ATTR_SCALE` | The scale of numeric arguments. If the precision is nonzero and scale is -127, then it is a `FLOAT`, else it is a `NUMBER(precision, scale)`. For the case when precision is 0, `NUMBER(precision, scale)` can be represented simply as `NUMBER`. | `sb1`
`OCI_ATTR_LEVEL` | The datatype levels. This attribute always returns zero. |
    ub2
`OCI_ATTR_HAS_DEFAULT` | Indicates whether an argument has a default |
    ub1
`OCI_ATTR_LIST_ARGUMENTS` | The list of arguments at the next level (when the argument is of a record or table type). |
    dvoid *
`OCI_ATTR_IOMODE` | Indicates the argument mode: 0 is IN (`OCI_TYPEPARAM_IN`), 1 is OUT (`OCI_TYPEPARAM_OUT`), 2 is IN/OUT (`OCI_TYPEPARAM_INOUT`) |
    OCITypeParamMode
`OCI_ATTR_RADIX` | Returns a radix (if number type) |
    ub1
`OCI_ATTR_IS_NULL` | Returns 0 if null values are not permitted for the column |
    ub1
`OCI_ATTR_TYPE_NAME` | Returns a string which is the type name, or the package name in the case of package local types. The returned value will contain the type name if the datatype is SQLT_NTY or SQLT_REF. If the datatype is SQLT_NTY, the name of the named datatype's type is returned. If the datatype is SQLT_REF, the type name of the named datatype pointed to by the REF is returned. |
    OraText *
`OCI_ATTR_SCHEMA_NAME` | For SQLT_NTY or SQLT_REF, returns a string with the schema name under which the type was created, or under which the package was created in the case of package local types |
    OraText *
`OCI_ATTR_SUB_NAME` | For SQLT_NTY or SQLT_REF, returns a string with the type name, in the case of package local types |
    OraText *
`OCI_ATTR_LINK` | For SQLT_NTY or SQLT_REF, returns a string with the database link name of the database on which the type exists. This can happen only in the case of package local types, when the package is remote. |
    OraText *
`OCI_ATTR_REF_TDO` | Returns the REF of the TDO for the type, if the argument type is an object |
    OCIRef *
`OCI_ATTR_CHARSET_ID` | Returns the character set ID if the argument is of a string/character type |
    ub2
`OCI_ATTR_CHARSET_FORM` | Returns the character set form if the argument is of a string/character type |
    ub1

### List Attributes

When a parameter is for a list of columns, arguments, or subprograms (type `OCI_PTYPE_LIST`), it has the following type specific attributes and handles (parameters):

The list has an `OCI_ATTR_LIST_TYPE` attribute which designates the list type. The possible values and their lower bounds when traversing the list are:

Table 6-15 List Attributes

List Attribute | Description | Lower Bound
--- | --- | ---
`OCI_LTYPE_COLUMN` | Column list | 1
`OCI_LTYPE_ARG_PROC` | Procedure argument list | 1
`OCI_LTYPE_ARG_FUNC` | Function argument list | 0
`OCI_LTYPE_SUBPRG` | Subprogram list | 0
`OCI_LTYPE_TYPE_ATTR` | Type attribute list | 1
`OCI_LTYPE_TYPE_METHOD` | Type method list | 1
`OCI_LTYPE_TYPE_ARG_PROC` | Type method without result argument list | 0
`OCI_LTYPE_TYPE_ARG_FUNC` | Type method without result argument list | 1
`OCI_LTYPE_SCH_OBJ` | Object list within a schema | 0
`OCI_LTYPE_DB_SCH` | Schema list within a database | 0

- The list has an `OCI_ATTR_NUM_PARAMS` attribute, which tells the number of elements in the list.

- Each list has `LowerBound` .. `OCI_ATTR_NUM_PARAMS` parameters. `LowerBound` is the value in the Lower Bound column of Table 6-15, "List Attributes". In the case of a function argument list, position 0 has a parameter for the return value (type `OCI_PTYPE_ARG`).

### Schema Attributes

When a parameter is for a schema type (type `OCI_PTYPE_SCHEMA`), it has the attributes listed in Table 6-16:

Table 6-16 Attributes Specific to Schemas

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_LIST_OBJECTS` | List of objects in the schema |
    OraText *

### Database Attributes

When a parameter is for a database type (type `OCI_PTYPE_DATABASE`), it has the attributes listed in Table 6-17:

Table 6-17 Attributes Specific to Databases

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_VERSION` | Database version |
    OraText *
`OCI_ATTR_CHARSET_ID` | Database character set Id from the server handle |
    ub2
`OCI_ATTR_NCHARSET_ID` | Database character set Id from the server handle |
    ub2
`OCI_ATTR_LIST_SCHEMAS` | List of schemas (type `OCI_PTYPE_SCHEMA`) in the database |
    ub1
`OCI_ATTR_MAX_PROC_LEN` | Maximum length of a procedure name |
    ub4
`OCI_ATTR_MAX_COLUMN_LEN` | Maximum length of a column name |
    ub4
`OCI_ATTR_CURSOR_COMMIT_BEHAVIOR` | How a COMMIT operation affects cursors and prepared statements in the database. Values are: `OCI_CURSOR_OPEN` - preserve cursor state as before the commit operation `OCI_CURSOR_CLOSED` - cursors are closed on COMMIT, but the application can still re-execute the statement without re-preparing it |
    ub1
`OCI_ATTR_MAX_CATALOG_NAMELEN` | Maximum length of a catalog (database) name |
    ub1
`OCI_ATTR_CATALOG_LOCATION` | Position of the catalog in a qualified table. Values are `OCI_CL_START` and `OCI_CL_END` |
    ub1
`OCI_ATTR_SAVEPOINT_SUPPORT` | Does database support savepoints? Values are `OCI_SP_SUPPORTED` and `OCI_SP_UNSUPPORTED` |
    ub1
`OCI_ATTR_NOWAIT_SUPPORT` | Does database support the nowait clause? Values are `OCI_NW_SUPPORTED` and `OCI_NW_UNSUPPORTED` |
    ub1
`OCI_ATTR_AUTOCOMMIT_DDL` | Is autocommit mode required for DDL statements? Values are `OCI_AC_DDL` and `OCI_NO_AC_DDL` |
    ub1
`OCI_ATTR_LOCKING_MODE` | Locking mode for the database. Values are `OCI_LOCK_IMMEDIATE` and `OCI_LOCK_DELAYED` |
    ub1

### Rule Attributes

When a parameter is for a rule (type `OCI_PTYPE_RULE`), it has the attributes listed in Table 6-18:

Table 6-18 Attributes Specific to Rules

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_CONDITION` | Rule condition |
    OraText *
`OCI_ATTR_EVAL_CONTEXT_OWNER` | Owner name of evaluation context associated with the rule, if any |
    OraText *
`OCI_ATTR_EVAL_CONTEXT_NAME` | Object name of evaluation context associated with the rule, if any |
    OraText *
`OCI_ATTR_COMMENT` | Comment associated with the rule, if any |
    OraText *
`OCI_ATTR_LIST_ACTION_CONTEXT` | List of name value pairs in the action context (type `OCI_PTYPE_LIST`) |
    dvoid *

### Rule Set Attributes

When a parameter is for a rule set (type `OCI_PTYPE_RULE_SET`), it has the attributes listed in Table 6-19:

Table 6-19 Attributes Specific to Rule Sets

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_EVAL_CONTEXT_OWNER` | Owner name of evaluation context associated with the rule set, if any |
    OraText *
`OCI_ATTR_EVAL_CONTEXT_NAME` | Object name of evaluation context associated with the rule set, if any |
    OraText *
`OCI_ATTR_COMMENT` | Comment associated with the rule set, if any |
    OraText *
`OCI_ATTR_LIST_RULES` | List of rules in the rule set (type `OCI_PTYPE_LIST`) |
    dvoid *

### Evaluation Context Attributes

When a parameter is for an evaluation context (type `OCI_PTYPE_EVALUATION_CONTEXT`), it has the attributes listed in Table 6-20:

Table 6-20 Attributes Specific to Evaluation Contexts

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_EVALUATION_FUNCTION` | Evaluation function associated with the evaluation context, if any |
    OraText *
`OCI_ATTR_COMMENT` | Comment associated with the evaluation context, if any |
    OraText *
`OCI_ATTR_LIST_TABLE_ALIASES` | List of table aliases in the evaluation context (type `OCI_PTYPE_LIST`) |
    dvoid *
`OCI_ATTR_LIST_VARIABLE_TYPES` | List of variable types in the evaluation context (type `OCI_PTYPE_LIST`) |
    dvoid *

### Table Alias Attributes

When a parameter is for a table alias (type `OCI_PTYPE_TABLE_ALIAS`), it has the attributes listed in Table 6-21:

Table 6-21 Attributes Specific to Table Aliases

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_NAME` | Table alias name |
    OraText *
`OCI_ATTR_TABLE_NAME` | Table name associated with the alias |
    OraText *

### Variable Type Attributes

When a parameter is for a variable (type `OCI_PTYPE_VARIABLE_TYPE`), it has the attributes listed in Table 6-22:

Table 6-22 Attributes Specific to Variable Types

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_NAME` | Variable name |
    OraText *
`OCI_ATTR_TYPE` | Variable type |
    OraText *
`OCI_ATTR_VAR_VALUE_FUNCTION` | Variable value function associated with the variable, if any |
    OraText *
`OCI_ATTR_VAR_METHOD_FUNCTION` | Variable method function associated with the variable, if any |
    OraText *

### Name Value Attributes

When a parameter is for a name value pair (type `OCI_PTYPE_NAME_VALUE`), it has the attributes listed in Table 6-23:

Table 6-23 Attributes Specific to Name Value Pair

Attribute | Description | Attribute Datatype
--- | --- | ---
`OCI_ATTR_NAME` | Name |
    OraText *
`OCI_ATTR_VALUE` | Value |
    OCIAnyData*

## Character Length Semantics Support in Describing

Since release Oracle9i, query and column information are supported with character length semantics.

The following attributes of describe handles support character length semantics:

- OCI_ATTR_CHAR_SIZE gets the column character length, which is the number of characters allowed in the column. It is the counterpart of `OCI_ATTR_DATA_SIZE` that gets the byte length.

- Calling `OCIAttrGet()` with attribute `OCI_ATTR_CHAR_SIZE` or `OCI_ATTR_DATA_SIZE` does not return data on stored procedure parameters, because stored procedure parameters are not bounded.

- `OCI_ATTR_CHAR_USED` gets the type of length semantics of the column. 0 means byte-length semantics and 1 means character length semantics.

An application can describe a select-list query either implicitly or explicitly through `OCIStmtExecute()`. Other schema elements must be described explicitly through `OCIDescribeAny()`.

### Implicit Describing

If the database column was created using character length semantics, then the implicit describe information will contain the character length, the byte length, and a flag indicating how the database column was created. `OCI_ATTR_CHAR_SIZE` is the character length of the column or expression. The `OCI_ATTR_CHAR_USED` flag is 1 in this case, indicating that the column or expression was created with character length semantics.

The `OCI_ATTR_DATA_SIZE` value will be always large enough to hold all the data, as many as `OCI_ATTR_CHAR_SIZE` number of characters. The `OCI_ATTR_DATA_SIZE` will be usually set to (`OCI_ATTR_CHAR_SIZE`)*(the client's max bytes) for each character value.

If the database column was created with byte length semantics, then the implicit describe will behave exactly as it does before release 9.0. That is, the `OCI_ATTR_DATA_SIZE` value returned will be (column's byte length)*(the maximum conversion ratio between the client and server's character set), that is, column byte length divided by the server's max bytes for each character multiplied by the client's max bytes for each character. The `OCI_ATTR_CHAR_USED` value is 0 and the `OCI_ATTR_CHAR_SIZE` value will be set to the same value as `OCI_ATTR_DATA_SIZE`.

### Explicit Describing

Explicit describes of tables will have the following attributes:

- `OCI_ATTR_DATA_SIZE` that gets the column's size in bytes, as it appears in the server

- the length in characters in `OCI_ATTR_CHAR_SIZE`

- a flag `OCI_ATTR_CHAR_USED` that indicates how the column was created

When inserting, if the `OCI_ATTR_CHAR_USED` flag is set, you can set the `OCI_ATTR_MAXCHAR_SIZE` in the bind handle to the value returned by `OCI_ATTR_CHAR_SIZE` in the parameter handle. This will prevent you from violating the size constraint for the column.

See Also: "IN Binds"

#### Client and Server Compatibility Issues for Describing

When an Oracle9i or later client talks to an Oracle8i or earlier server, it will behave as if the database is only using byte length semantics;

When an Oracle8i or earlier client talks to a Oracle9i or later server, the attributes `OCI_ATTR_CHAR_SIZE` and `OCI_ATTR_CHAR_USED` are not available on the client side.

In both cases, the character length semantics cannot be described when either the server or client has an Oracle8i or earlier software release.

## Examples Using OCIDescribeAny()

The following examples demonstrate the use of `OCIDescribeAny()` for describing different types of schema objects. For a more detailed code sample, see the demonstration program `cdemodsa.c` included with your Oracle installation.

See Also: For additional information on the demonstration programs, see Appendix B, "OCI Demonstration Programs"

### Retrieving Column Datatypes for a Table

This example illustrates the use of an explicit describe that retrieves the column datatypes for a table.

    ...
    int i=0;
    text objptr[] = "EMPLOYEES"; /* the name of a table to be described */
    ub2          numcols, col_width;
    ub1          char_semantics;
    ub2  coltyp;
    ub4 objp_len = (ub4) strlen((char *)objptr);
    OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */
    OCIParam *collsthd = (OCIParam *) 0;      /* handle to list of columns */
    OCIParam *colhd = (OCIParam *) 0;         /* column handle */
    OCIDescribe *dschp = (OCIDescribe *)0;      /* describe handle */

    OCIHandleAlloc((dvoid *)envhp, (dvoid **)&dschp,
            (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0);

    /* get the describe handle for the table */
    if (OCIDescribeAny(svch, errh, (dvoid *)objptr, objp_len, OCI_OTYPE_NAME, 0,
         OCI_PTYPE_TABLE, dschp))
       return OCI_ERROR;

    /* get the parameter handle */
    if (OCIAttrGet((dvoid *)dschp, OCI_HTYPE_DESCRIBE, (dvoid *)&parmh, (ub4 *)0,
                    OCI_ATTR_PARAM, errh))
        return OCI_ERROR;

    /* The type information of the object, in this case, OCI_PTYPE_TABLE,
    is obtained from the parameter descriptor returned by the OCIAttrGet(). */
    /* get the number of columns in the table */
    numcols = 0;
    if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&numcols, (ub4 *)0,
         OCI_ATTR_NUM_COLS, errh))
        return OCI_ERROR;

    /* get the handle to the column list of the table */
    if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&collsthd, (ub4 *)0,
         OCI_ATTR_LIST_COLUMNS, errh)==OCI_NO_DATA)
       return OCI_ERROR;

    /* go through the column list and retrieve the data-type of each column,
    and then recursively describe column types. */

    for (i = 1; i <= numcols; i++)
    {
        /* get parameter for column i */
        if (OCIParamGet((dvoid *)collsthd, OCI_DTYPE_PARAM, errh, (dvoid **)&colhd, (ub4)i))
            return OCI_ERROR;

        /* for example, get datatype for ith column */
        coltyp = 0;
        if (OCIAttrGet((dvoid *)colhd, OCI_DTYPE_PARAM, (dvoid *)&coltyp, (ub4 *)0,
           OCI_ATTR_DATA_TYPE, errh))
            return OCI_ERROR;

        /* Retrieve the length semantics for the column */
        char_semantics = 0;
        OCIAttrGet((dvoid*) colhd, (ub4) OCI_DTYPE_PARAM,
                     (dvoid*) &char_semantics,(ub4 *) 0, (ub4) OCI_ATTR_CHAR_USED,
                     (OCIError *) errh);

        col_width = 0;
        if (char_semantics)
            /* Retrieve the column width in characters */
            OCIAttrGet((dvoid*) colhd, (ub4) OCI_DTYPE_PARAM,
                     (dvoid*) &col_width, (ub4 *) 0, (ub4) OCI_ATTR_CHAR_SIZE,
                     (OCIError *) errh);
        else
            /* Retrieve the column width in bytes */
            OCIAttrGet((dvoid*) colhd, (ub4) OCI_DTYPE_PARAM,
                     (dvoid*) &col_width,(ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE,
                     (OCIError *) errh);
    }

    if (dschp)
        OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
    ...

### Describing the Stored Procedure

The difference between a procedure and a function is that the latter has a return type at position 0 in the argument list, while the former has no argument associated with position 0 in the argument list. The steps required to describe type methods (also divided into functions and procedures) are identical to that of regular PL/SQL functions and procedures. Note that procedures and functions can take default types of objects as arguments. Consider the following procedure:

    P1 (arg1 emp.sal%type, arg2 emp%rowtype)

Assume that each row in emp table has two columns: `name(VARCHAR2(20))`, and `sal(NUMBER)`. In the argument list for `P1`, there are two arguments, `arg1` and `arg2`, at positions 1 and 2 respectively at level 0, and arguments name and `sal` at positions 1and 2 respectively at level 1. Description of `P1` returns the number of arguments as two while returning the higher level (> 0) arguments as attributes of the 0 zero level arguments.

    ...
    int i = 0, j = 0;
    text objptr[] = "add_job_history"; /* the name of a procedure to be described */
    ub4 objp_len = (ub4)strlen((char *)objptr);
    ub2 numargs = 0, numargs1, pos, level;
    text *name, *name1;
    ub4 namelen, namelen1;
    OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */
    OCIParam *arglst = (OCIParam *) 0;          /* list of args */
    OCIParam *arg = (OCIParam *) 0;             /* argument handle */
    OCIParam *arglst1 = (OCIParam *) 0;          /* list of args */
    OCIParam *arg1 = (OCIParam *) 0;             /* argument handle */
    OCIDescribe *dschp = (OCIDescribe *)0;      /* describe handle */

    OCIHandleAlloc((dvoid *)envhp, (dvoid **)&dschp,
            (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0);

    /* get the describe handle for the procedure */
    if (OCIDescribeAny(svch, errh, (dvoid *)objptr, objp_len, OCI_OTYPE_NAME, 0,
         OCI_PTYPE_PROC, dschp))
       return OCI_ERROR;

    /* get the parameter handle */
    if (OCIAttrGet((dvoid *)dschp, OCI_HTYPE_DESCRIBE, (dvoid *)&parmh, (ub4 *)0,
             OCI_ATTR_PARAM, errh))
        return OCI_ERROR;

    /* Get the number of arguments and the arg list */
    if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&arglst,
           (ub4 *)0, OCI_ATTR_LIST_ARGUMENTS, errh))
        return OCI_ERROR;

    if (OCIAttrGet((dvoid *)arglst, OCI_DTYPE_PARAM, (dvoid *)&numargs, (ub4 *)0,
        OCI_ATTR_NUM_PARAMS, errh))
        return OCI_ERROR;

    /* For a procedure, we begin with i = 1; for a
    function, we begin with i = 0. */

    for (i = 1; i <= numargs; i++) {
      OCIParamGet ((dvoid *)arglst, OCI_DTYPE_PARAM, errh, (dvoid **)&arg, (ub4)i);
      namelen = 0;
      OCIAttrGet((dvoid *)arg, OCI_DTYPE_PARAM, (dvoid *)&name, (ub4 *)&namelen,
           OCI_ATTR_NAME, errh);

      /* to print the attributes of the argument of type record
      (arguments at the next level), traverse the argument list */

      OCIAttrGet((dvoid *)arg, OCI_DTYPE_PARAM, (dvoid *)&arglst1, (ub4 *)0,
            OCI_ATTR_LIST_ARGUMENTS, errh);

      /* check if the current argument is a record. For arg1 in the procedure
      arglst1 is NULL. */

      if (arglst1) {
        numargs1 = 0;
        OCIAttrGet((dvoid *)arglst1, OCI_DTYPE_PARAM, (dvoid *)&numargs1, (ub4 *)0,
             OCI_ATTR_NUM_PARAMS, errh);

        /* Note that for both functions and procedures,the next higher level
        arguments start from index 1. For arg2 in the procedure, the number of
        arguments at the level 1 would be 2 */

        for (j = 1; j <= numargs1; j++) {
          OCIParamGet((dvoid *)arglst1, OCI_DTYPE_PARAM, errh, (dvoid **)&arg1,
              (ub4)j);
          namelen1 = 0;
          OCIAttrGet((dvoid *)arg1, OCI_DTYPE_PARAM, (dvoid *)&name1, (ub4 *)&namelen1,
            OCI_ATTR_NAME, errh);
        }
      }
    }

    if (dschp)
        OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
    ...

### Retrieving Attributes of an Object Type

This example illustrates the use of an explicit describe on a named object type. We illustrate how you can describe an object by its name or by its object reference (`OCIRef`). The following code fragment attempts to retrieve the datatype value of each of the object type's attributes.

    ...
    int i = 0;
    text type_name[] = "inventory_typ";
    ub4 type_name_len = (ub4)strlen((char *)type_name);
    OCIRef *type_ref = (OCIRef *) 0;
    ub2 numattrs = 0, describe_by_name = 1;
    ub2 datatype = 0;
    OCITypeCode typecode = 0;
    OCIDescribe *dschp = (OCIDescribe *) 0;      /* describe handle */
    OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */
    OCIParam *attrlsthd = (OCIParam *) 0;     /* handle to list of attrs */
    OCIParam *attrhd = (OCIParam *) 0;        /* attribute handle */

    /* allocate describe handle */
    if (OCIHandleAlloc((dvoid *)envh, (dvoid **)&dschp,
                      (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0))
       return OCI_ERROR;

    /* get the describe handle for the type */
    if (describe_by_name) {
      if (OCIDescribeAny(svch, errh, (dvoid *)type_name, type_name_len,
           OCI_OTYPE_NAME, 0, OCI_PTYPE_TYPE, dschp))
           return OCI_ERROR;
    }
    else {
      /* get ref to type using OCIAttrGet */

      /* get the describe handle for the type */
      if (OCIDescribeAny(svch, errh, (dvoid*)type_ref, 0, OCI_OTYPE_REF,
            0, OCI_PTYPE_TYPE, dschp))
           return OCI_ERROR;
    }

    /* get the parameter handle */
    if (OCIAttrGet((dvoid *)dschp, OCI_HTYPE_DESCRIBE, (dvoid *)&parmh, (ub4 *)0,
            OCI_ATTR_PARAM, errh))
        return OCI_ERROR;

    /* The type information of the object, in this case, OCI_PTYPE_TYPE, is
    obtained from the parameter descriptor returned by the OCIAttrGet */

    /* get the number of attributes in the type */
    if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&numattrs, (ub4 *)0,
        OCI_ATTR_NUM_TYPE_ATTRS, errh))
        return OCI_ERROR;

    /* get the handle to the attribute list of the type */
    if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&attrlsthd, (ub4 *)0,
          OCI_ATTR_LIST_TYPE_ATTRS, errh))
      return OCI_ERROR;

    /* go through the attribute list and retrieve the data-type of each attribute,
    and then recursively describe attribute types. */

    for (i = 1; i <= numattrs; i++)
    {
    /* get parameter for attribute i */
    if (OCIParamGet((dvoid *)attrlsthd, OCI_DTYPE_PARAM, errh, (dvoid **)&attrhd, i))
          return OCI_ERROR;

    /* for example, get datatype and typecode for attribute; note that
    OCI_ATTR_DATA_TYPE returns the SQLT code, while OCI_ATTR_TYPECODE returns the
    Oracle Type System typecode. */

    datatype = 0;
    if (OCIAttrGet((dvoid *)attrhd, OCI_DTYPE_PARAM, (dvoid *)&datatype, (ub4 *)0,
                   OCI_ATTR_DATA_TYPE,errh))
        return OCI_ERROR;

    typecode = 0;
    if (OCIAttrGet((dvoid *)attrhd, OCI_DTYPE_PARAM,(dvoid *)&typecode, (ub4 *)0,
                   OCI_ATTR_TYPECODE, errh))
        return OCI_ERROR;

    /* if attribute is an object type, recursively describe it */
    if (typecode == OCI_TYPECODE_OBJECT)
    {
      OCIRef *attr_type_ref;
      OCIDescribe *nested_dschp;

      /* allocate describe handle */
      if (OCIHandleAlloc((dvoid *)envh,(dvoid**)&nested_dschp,
        (ub4)OCI_HTYPE_DESCRIBE,(size_t)0, (dvoid **)0))
        return OCI_ERROR;

      if (OCIAttrGet((dvoid *)attrhd, OCI_DTYPE_PARAM,
              (dvoid *)&attr_type_ref, (ub4 *)0, OCI_ATTR_REF_TDO,errh))
        return OCI_ERROR;

       OCIDescribeAny(svch, errh,(dvoid*)attr_type_ref, 0,
              OCI_OTYPE_REF, 0, OCI_PTYPE_TYPE, nested_dschp);
        /* go on describing the attribute type... */
    }
    }

    if (dschp)
        OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
    ...

### Retrieving the Collection Element's Datatype of a Named Collection Type

This example illustrates the use of an explicit describe on a named collection type:

    text type_name[] = "phone_list_typ";
    ub4 type_name_len = (ub4) strlen((char *)type_name);
    OCIRef *type_ref = (OCIRef *) 0;
    ub2 describe_by_name = 1;
    ub4 num_elements = 0;
    OCITypeCode typecode = 0, collection_typecode = 0, element_typecode = 0;
    dvoid *collection_element_parmh = (dvoid *) 0;
    OCIDescribe *dschp = (OCIDescribe *) 0;      /* describe handle */
    OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */

    /* allocate describe handle */
    if (OCIHandleAlloc((dvoid *)envh, (dvoid **)&dschp,
                      (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0))
       return OCI_ERROR;

    /* get the describe handle for the type */
    if (describe_by_name) {
      if (OCIDescribeAny(svch, errh, (dvoid *)type_name, type_name_len,
           OCI_OTYPE_NAME, 0, OCI_PTYPE_TYPE, dschp))
           return OCI_ERROR;
    }
    else {
      /* get ref to type using OCIAttrGet */

      /* get the describe handle for the type */
      if (OCIDescribeAny(svch, errh, (dvoid*)type_ref, 0, OCI_OTYPE_REF,
            0, OCI_PTYPE_TYPE, dschp))
           return OCI_ERROR;
    }

    /* get the parameter handle */
    if (OCIAttrGet((dvoid *)dschp, OCI_HTYPE_DESCRIBE, (dvoid *)&parmh, (ub4 *)0,
            OCI_ATTR_PARAM, errh))
        return OCI_ERROR;

    /* get the Oracle Type System type code of the type to determine that this is a
    collection type */
    typecode = 0;
    if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM,(dvoid *)&typecode, (ub4 *)0,
          OCI_ATTR_TYPECODE, errh))
        return OCI_ERROR;

    /* if typecode is OCI_TYPECODE_NAMEDCOLLECTION,
      proceed to describe collection element */
    if (typecode == OCI_TYPECODE_NAMEDCOLLECTION)
    {
      /* get the collection's type: ie, OCI_TYPECODE_VARRAY or OCI_TYPECODE_TABLE */
      collection_typecode = 0;
      if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&collection_typecode,
     (ub4 *)0,
           OCI_ATTR_COLLECTION_TYPECODE, errh))
        return OCI_ERROR;

      /* get the collection element; you MUST use this to further retrieve information
         about the collection's element */
      if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, &collection_element_parmh,
                     (ub4 *)0,
            OCI_ATTR_COLLECTION_ELEMENT, errh))
        return OCI_ERROR;
      /* get the number of elements if collection is a VARRAY; not valid for nested
         tables */
      if (collection_typecode == OCI_TYPECODE_VARRAY) {
        if (OCIAttrGet((dvoid *)collection_element_parmh, OCI_DTYPE_PARAM,
              (dvoid *)&num_elements, (ub4 *)0, OCI_ATTR_NUM_ELEMS, errh))
          return OCI_ERROR;
      }
      /* now use the collection_element parameter handle to retrieve information about
         the collection element */
      element_typecode = 0;
      if (OCIAttrGet((dvoid *)collection_element_parmh, OCI_DTYPE_PARAM,
            (dvoid *)&element_typecode, (ub4 *)0, OCI_ATTR_TYPECODE, errh))
        return OCI_ERROR;

      /* do the same to describe additional collection element information; this is
      very similar to describing type attributes */
    }

    if (dschp)
        OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
    ...

### Describing with Character Length Semantics

The following sample code shows a loop that retrieves the column names and datatypes corresponding to a query following query execution. The query was associated with the statement handle by a prior call to `OCIStmtPrepare()`.

    ...
    OCIParam     *mypard = (OCIParam *) 0;
    ub2          dtype;
    text         *col_name;
    ub4          counter, col_name_len, char_semantics;
    ub2          col_width;
    sb4          parm_status;

    text *sqlstmt = (text *)"SELECT * FROM employees WHERE employee_id = 100";

    checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (OraText *)sqlstmt,
                        (ub4)strlen((char *)sqlstmt),
                        (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
    checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, 0, 0, (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT));

    /* Request a parameter descriptor for position 1 in the select-list */
    counter = 1;
    parm_status = OCIParamGet((dvoid *)stmthp, OCI_HTYPE_STMT, errhp,
                   (dvoid **)&mypard, (ub4) counter);
    /* Loop only if a descriptor was successfully retrieved for
       current position, starting at 1 */
    while (parm_status == OCI_SUCCESS) {
       /* Retrieve the datatype attribute */
       checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
               (dvoid*) &dtype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE,
               (OCIError *) errhp  ));
       /* Retrieve the column name attribute */
       col_name_len = 0;
       checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
               (dvoid**) &col_name, (ub4 *) &col_name_len, (ub4) OCI_ATTR_NAME,
               (OCIError *) errhp ));
       /* Retrieve the length semantics for the column */
       char_semantics = 0;
       checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
               (dvoid*) &char_semantics,(ub4 *) 0, (ub4) OCI_ATTR_CHAR_USED,
               (OCIError *) errhp  ));
       col_width = 0;
       if (char_semantics)
           /* Retrieve the column width in characters */
           checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
                   (dvoid*) &col_width, (ub4 *) 0, (ub4) OCI_ATTR_CHAR_SIZE,
                   (OCIError *) errhp  ));
       else
           /* Retrieve the column width in bytes */
           checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
                   (dvoid*) &col_width,(ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE,
                   (OCIError *) errhp  ));
       /* increment counter and get next descriptor, if there is one */
       counter++;
       parm_status = OCIParamGet((dvoid *)stmthp, OCI_HTYPE_STMT, errhp,
              (dvoid **)&mypard, (ub4) counter);
    } /* while */
    ...